// Ofri Keidar 302933981 group 05

package controller;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import model.SystemDataManager;
import model.database.DBException;
import model.database.StoreDB;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import controller.SystemController;

/**
 * Class to test all of employees's transactions.
 * Class adds test record to database, and uses system's controller to run
 * the transactions and check the results.
 * IMPORTANT:
 * database must be empty before running tests, because expected values are
 * based on test records only
 */
public class EmployeeControllerTester {
	
	/**
	 * Method that runs before the first test.
	 * This method enters to database the necessary test records.
	 * Test records are:
	 * 1. customer
	 * 2. user-name for customer
	 * 3. employee
	 * 4. user-name for employee
	 * 5. product
	 */
	@BeforeClass
	public static void setup()  {

		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();
		
    	// set server and database        	
    	ds.setServerName(TestCommons.SERVER_NAME);
    	ds.setDatabaseName(TestCommons.DB_NAME);

		try {
			
			// connect to database
			connection = ds.getConnection(TestCommons.DB_USER, TestCommons.DB_PWD);
			Statement statement = connection.createStatement();
			
			// create a customer record
			// create query string
			String query = String
					.format("INSERT INTO %s (%s, %s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s', '%s')",
							TestCommons.CUSTOMERS_TABLE, TestCommons.NUMBER,
							TestCommons.NAME, TestCommons.USER_NAME, TestCommons.ADDRESS,
							TestCommons.PAYMENT_METHOD, TestCommons.TEST_NUMBER,
							TestCommons.TEST_NAME,
							TestCommons.TEST_CUSTOMER_USER_NAME,
							TestCommons.TEST_ADDRESS,
							TestCommons.TEST_PAYMENT_METHOD);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer
				fail();
			}
			
			// set user-name for customer
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME,
					TestCommons.PASSWORD, TestCommons.PERMISSION,
					TestCommons.TEST_CUSTOMER_USER_NAME,
					TestCommons.TEST_PASSWORD,
					CommonValues.permissions.CUSTOMER.ordinal());
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer user-name
				fail();
			}
			
			// create an employee record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					TestCommons.EMPLOYEES_TABLE, TestCommons.NUMBER, TestCommons.NAME,
					TestCommons.USER_NAME, TestCommons.TEST_NUMBER,
					TestCommons.TEST_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add employee
				fail();
			}
			
			// set user-name for employee
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME,
					TestCommons.PASSWORD, TestCommons.PERMISSION,
					TestCommons.TEST_EMPLOYEE_USER_NAME,
					TestCommons.TEST_PASSWORD,
					CommonValues.permissions.EMPLOYEE.ordinal());

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add employee user-name
				fail();
			}
			
			// create a product record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s) VALUES ('%s', '%s')",
					TestCommons.PRODUCTS_TABLE, TestCommons.NAME, TestCommons.PRICE,
					TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
		} catch (SQLException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// tests should fail in case of exception
			fail();
			
		} finally {
			
			// close connection
			if (connection != null) {
				try {
					
					connection.close();
					
				} catch (SQLException e) {
					
					// check reason for error
					e.printStackTrace();
					
					// tests should fails in case of exception
					fail();
				}
			}
		}
	}

	/**
	 * Method that after the last test.
	 * This method deletes from database all test records
	 * that were added in setup()
	 */
	@AfterClass
	public static void teardown() {
		
		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();

		// set server and database
		ds.setServerName(TestCommons.SERVER_NAME);
		ds.setDatabaseName(TestCommons.DB_NAME);

		try {
			
			// connect to database
			connection = ds.getConnection(TestCommons.DB_USER, TestCommons.DB_PWD);
			Statement statement = connection.createStatement();
			
			// delete test customer record
			String query = String.format("DELETE FROM %s WHERE %s = %s", 
					TestCommons.CUSTOMERS_TABLE, TestCommons.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer
				fail();
			}
			
			// delete test customer's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME, TestCommons.TEST_CUSTOMER_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer's user
				fail();
			}
			
			// delete test employee record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					TestCommons.EMPLOYEES_TABLE, TestCommons.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee
				fail();
			}
			
			// delete test employee's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee's user
				fail();
			}

			// delete test product record
			query = String.format("DELETE FROM %s WHERE %s = '%s' ", 
					TestCommons.PRODUCTS_TABLE, TestCommons.NAME, TestCommons.TEST_PRODUCT_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete product
				fail();
			}
			
		} catch (SQLException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// tests should fail in case of exception
			fail();
			
		} finally {
			
			// close connection
			if (connection != null) {				
				try {
					
					connection.close();
					
				} catch (SQLException e) {
					
					// check reason for error
					e.printStackTrace();
				}
			}
		}
		
	}

	/**
	 * Adds a new customer and verifies that transaction's returned value
	 * is success.
	 * After adding, deletes the new customer in order to keep data consistent 
	 */
	@Test
	public void addCustomer() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add customer
			int newCustomerNum = TestCommons.TEST_NUMBER+1;
			String newCustomerUserName = TestCommons.TEST_USER_NAME + "_new";			 
			boolean couldAdd = controller.createCustomerAccount(newCustomerNum,
					TestCommons.TEST_NAME, newCustomerUserName,
					TestCommons.TEST_ADDRESS, TestCommons.TEST_PAYMENT_METHOD);
			
			// delete customer (for test-suite maintenance)
			
			// connection to database
			Connection connection = null;
			MysqlDataSource ds = new MysqlConnectionPoolDataSource();

			// set server and database
			ds.setServerName(TestCommons.SERVER_NAME);
			ds.setDatabaseName(TestCommons.DB_NAME);

			try {
		
				// connect to database
				connection = ds.getConnection(TestCommons.DB_USER, TestCommons.DB_PWD);
				Statement statement = connection.createStatement();
				
				// query to delete customer record
				String query = String.format("DELETE FROM %s WHERE %s = %s", 
						TestCommons.CUSTOMERS_TABLE, TestCommons.NUMBER, newCustomerNum);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete customer
					fail();
				}
				
				// query to delete customer's user-name record
				query = String.format("DELETE FROM %s WHERE %s = '%s'", 
						TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME, newCustomerUserName);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete customer
					fail();
				}
				
			} catch (SQLException e) {
			
				// check reason for error
				e.printStackTrace();
				
				// test should fail in case of exception
				fail();
				
			} finally {
				
				// close connection
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						
						// check reason for error
						e.printStackTrace();
						
						// test should fail in case of exception
						fail();
					}
				}
			}
			
			// compare values
			assertEquals(true, couldAdd);
			
		} catch (DBException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}
	}

	/**
	 * Adds an existing customer (same record from setup()) and verifies that
	 * transaction's returned value is not success
	 */
	@Test
	public void addExistingCustomer() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add customer (same as in setup)			 
			boolean couldAdd = controller.createCustomerAccount(
					TestCommons.TEST_NUMBER, TestCommons.TEST_NAME,
					TestCommons.TEST_CUSTOMER_USER_NAME,
					TestCommons.TEST_ADDRESS, TestCommons.TEST_PAYMENT_METHOD);
			
			// compare values
			assertEquals(false, couldAdd);
			
		} catch (DBException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}
	}

	/**
	 * Adds a new product and verifies that transaction's returned value
	 * is success.
	 * After adding, deletes the new product, in order to keep data consistent
	 */
	@Test
	public void addProduct() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add product (same as in setup)
			String newProductName = "new product";
			boolean couldAdd = controller.addNewProduct(newProductName, TestCommons.TEST_PRODUCT_PRICE);

			// delete product (for test-suite maintenance)
			
			// connection to database
			Connection connection = null;
			MysqlDataSource ds = new MysqlConnectionPoolDataSource();

			// set server and database
			ds.setServerName(TestCommons.SERVER_NAME);
			ds.setDatabaseName(TestCommons.DB_NAME);

			try {
				
				// delete new product
				
				// connect to database
				connection = ds.getConnection(TestCommons.DB_USER, TestCommons.DB_PWD);
				Statement statement = connection.createStatement();
				
				String query = String.format("DELETE FROM %s WHERE %s = '%s'", 
						TestCommons.PRODUCTS_TABLE, TestCommons.NAME, newProductName);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete order
					fail();
				}
				
			} catch (SQLException e) {

				// check reason for error
				e.printStackTrace();
				
				// test should fail in case of exception
				fail();
				
			} finally {
				
				// close connection
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
				
						// check reason for error
						e.printStackTrace();
						
						// test should fail in case of exception
						fail();
					}
				}
			}

			// compare values
			assertEquals(true, couldAdd);

		} catch (DBException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}
	}

	/**
	 * Adds an existing product (same record from setup()) and verifies that
	 * transaction's returned value is not success
	 */
	@Test
	public void addExistingProduct() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add product (same as in setup)
			boolean couldAdd = controller.addNewProduct(TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE);
			
			// compare values
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}
	}

	/**
	 * Updates test product's details, and verifies that
	 * transaction's return value is success.
	 * After updating for new details, sets the details
	 * back to default
	 */
	@Test
	public void updateProductPriceTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT EXISTS
			boolean couldUpdate = controller.updateProductPrice(TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE*2);
			
			// update back the details
			controller.updateProductPrice(TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE);
	
			// compare values
			assertEquals(true, couldUpdate);

		} catch (DBException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}
	}
	
	/**
	 * Tries updating details of non-existing product, 
	 * and verifies that transaction's return value is not success.
	 */
	@Test
	public void updateNonExistingProductPriceTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT DOES NOT EXIST
			boolean couldUpdate = controller.updateProductPrice("blah blah", TestCommons.TEST_PRODUCT_PRICE);
			
			assertEquals(false, couldUpdate);

		} catch (DBException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}
	}
}